{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "Possible affected SP\r\n",
                "MergedVendor\r\n",
                "MergedSalesDocument\r\n",
                "MergedItem\r\n",
                "MergedItemVariant\r\n",
                "MergedDivision\r\n",
                "MergedSalesBudget\r\n",
                "MergedCustomer\r\n",
                "MergedStaff\r\n",
                "MergedGlobalDim2\r\n",
                "MergedReturnReason\r\n",
                "MergedMembershipCard\r\n",
                "MergedMember\r\n",
                "MergedStore\r\n",
                "MergedProductGroup\r\n",
                "MergedGlobalDim1\r\n",
                "MergedCountryRegion\r\n",
                "MergedLocation\r\n",
                "MergedOffers\r\n",
                "MergedMemberAttributes\r\n",
                "MergedBusinessPostingGroup\r\n",
                "MergedPOSTerminal\r\n",
                "MergedItemCategory\r\n",
                "MergedProductPostingGroup\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b0e21ef3-a13e-46cb-8c38-285e9faefbc9"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedBusinessPostingGroup]"
            ],
            "metadata": {
                "azdata_cell_guid": "1537d5c1-1b62-4974-a465-c1f01a2e9c07"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedBusinessPostingGroup] \n",
                "@BusinessPostingGroup [dbo].[BusinessPostingGroupType] ReadOnly\n",
                " AS \n",
                "Begin\n",
                "With MaxRowID as(\n",
                "     Select MAX([RowID]) AS [RowID], [Company]\n",
                "\t   ,[Code] from [Stg].[BusinessPostingGroup]\n",
                "       group by [Company]\n",
                "\t   ,[Code]\n",
                " ),\n",
                " tBusinessPostingGroup as ( SELECT\n",
                "\t\t[Company]\n",
                "\t   ,[Code]\n",
                "\t   ,[Description]\n",
                "\t   ,MAX([RowID]) AS [RowID]\n",
                "\t   ,[Batchdate]\n",
                "\tFROM [Stg].[BusinessPostingGroup]\n",
                "\tGROUP BY [Company]\n",
                "\t\t\t,[Code]\n",
                "\t\t\t,[Description]\n",
                "\t\t\t,[Batchdate])\n",
                "MERGE DW.dBusinessPostingGroup AS Target\n",
                "USING ( \n",
                "  SELECT BPG.[Company]\n",
                "\t   ,BPG.[Code]\n",
                "\t   ,BPG.[Description]\n",
                "\t   ,Mr.[RowID]\n",
                "\t   ,BPG.[Batchdate] from tBusinessPostingGroup BPG\n",
                " Right join MaxRowID MR\n",
                " On BPG.Code = MR.[Code]\n",
                " And BPG.Company = MR.Company\n",
                " and BPG.RowID = MR.RowID) AS Source\n",
                "ON Target.[Code] = Source.[Code]\n",
                "WHEN MATCHED\n",
                "\tTHEN UPDATE\n",
                "\t\tSET [Description] = Source.[Description]\n",
                "\t\t   ,[RowID] = Source.[RowID]\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\n",
                "WHEN NOT MATCHED BY TARGET\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [RowID], [BatchDate])\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[RowID], Source.[BatchDate])\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\n",
                "WHEN NOT MATCHED BY SOURCE\n",
                "\tAND Target.SK_BusinessPostingGroup <> -1\n",
                "\tTHEN DELETE;\n",
                ";\n",
                "END\n",
                "SELECT\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "a867ebe2-d940-43a5-b5be-01080fc9a023",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.173"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedCountryRegion]"
            ],
            "metadata": {
                "azdata_cell_guid": "3a3951ce-0acd-4e5d-a2f4-4292b09fd78c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedCountryRegion] \r\n",
                "@CountryRegion [dbo].[CountryRegionType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "WITH MaxRowID as (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "    ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[CountryRegion]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t), \r\n",
                "tCountryRegion AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,[Name]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[BatchDate]\r\n",
                "FROM [Stg].[CountryRegion]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Name]\r\n",
                "\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dCountryRegion AS Target\r\n",
                "\r\n",
                "USING ( SELECT\r\n",
                "\t\tCR.[Company]\r\n",
                "\t   ,CR.[Code]\r\n",
                "\t   ,CR.[Name]\r\n",
                "\t   ,MR.[RowID]\r\n",
                "\t   ,CR.[BatchDate]\r\n",
                "\tFROM tCountryRegion CR\r\n",
                "Right join MaxRowID MR\r\n",
                " On CR.Code = MR.[Code]\r\n",
                " And CR.Company = MR.Company\r\n",
                " and CR.RowID = MR.RowID\r\n",
                ") AS Source\r\n",
                "\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Name] = Source.[Name]\r\n",
                "\t\t   ,[Company] = Source.Company\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [Code], [Name], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Name], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_CountryRegion <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "b6cdbe55-c776-4437-9120-ba6b90f0abac",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.063"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedCustomer]"
            ],
            "metadata": {
                "azdata_cell_guid": "22d1b05b-6dfb-44b3-9e19-e0cec3f9bd37"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedCustomer] \r\n",
                "@Customer [dbo].[CustomerType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "  FROM [Stg].[Customer]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]),\r\n",
                "\r\n",
                "tCustomer AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,[Name]\r\n",
                "   ,[NoName]\r\n",
                "   ,[Address]\r\n",
                "   ,[Address2]\r\n",
                "   ,[City]\r\n",
                "   ,[PostCode]\r\n",
                "   ,[County]\r\n",
                "   ,[CountryRegionCode]\r\n",
                "   ,[TerritoryCode]\r\n",
                "   ,[CreditLimit(LCY)]\r\n",
                "   ,[PaymentTermsCode]\r\n",
                "   ,[SalesPersonCode]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,CONVERT(DATE, GETUTCDATE()) AS BatchDate\r\n",
                "FROM [Stg].[Customer]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]\r\n",
                "\t\t,[Name]\r\n",
                "\t\t,[NoName]\r\n",
                "\t\t,[Address]\r\n",
                "\t\t,[Address2]\r\n",
                "\t\t,[City]\r\n",
                "\t\t,[PostCode]\r\n",
                "\t\t,[County]\r\n",
                "\t\t,[CountryRegionCode]\r\n",
                "\t\t,[TerritoryCode]\r\n",
                "\t\t,[CreditLimit(LCY)]\r\n",
                "\t\t,[PaymentTermsCode]\r\n",
                "\t\t,[SalesPersonCode])\r\n",
                "\r\n",
                "\r\n",
                "MERGE [DW].[dCustomer] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\tC.[Company]\r\n",
                "\t   ,C.[No]\r\n",
                "\t   ,C.[Name]\r\n",
                "\t   ,C.[NoName]\r\n",
                "\t   ,C.[Address]\r\n",
                "\t   ,C.[Address2]\r\n",
                "\t   ,C.[City]\r\n",
                "\t   ,C.[PostCode]\r\n",
                "\t   ,C.[County]\r\n",
                "\t   ,C.[CountryRegionCode]\r\n",
                "\t   ,C.[TerritoryCode]\r\n",
                "\t   ,C.[CreditLimit(LCY)]\r\n",
                "\t   ,C.[PaymentTermsCode]\r\n",
                "\t   ,C.[SalesPersonCode]\r\n",
                "\t   ,MR.[RowID]\r\n",
                "\t   ,CONVERT(DATE, GETUTCDATE()) AS BatchDate\r\n",
                "\tFROM tCustomer C\r\n",
                "RIGHT JOIN MaxRowID MR\r\n",
                "ON C.Company = MR.Company\r\n",
                "AND C.[No] = MR.[No]\r\n",
                "AND C.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "\r\n",
                "ON Target.[Company] = source.[Company]\r\n",
                "\tAND Target.[CustomerNo] = Source.[No]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Company] = source.[Company]\r\n",
                "\t\t   ,[CustomerNo] = source.[No]\r\n",
                "\t\t   ,[CustomerName] = source.[Name]\r\n",
                "\t\t   ,[CustomerNoName] = source.[NoName]\r\n",
                "\t\t   ,[Address] = source.[Address]\r\n",
                "\t\t   ,[Address2] = source.[Address2]\r\n",
                "\t\t   ,[City] = source.[City]\r\n",
                "\t\t   ,[Postcode] = source.[PostCode]\r\n",
                "\t\t   ,[County] = source.[County]\r\n",
                "\t\t   ,[CountryRegionCode] = source.[CountryRegionCode]\r\n",
                "\t\t   ,[TerritoryCode] = source.[TerritoryCode]\r\n",
                "\t\t   ,[CreditLimitLCY] = source.[CreditLimit(LCY)]\r\n",
                "\t\t   ,[PaymentTermsCode] = source.[PaymentTermsCode]\r\n",
                "\t\t   ,[SalesPersonCode] = source.[SalesPersonCode]\r\n",
                "\t\t   ,[RowID] = source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = source.[BatchDate]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [CustomerNo], [CustomerName], [CustomerNoName], [Address], [Address2], [City], [PostCode], [County], [TerritoryCode], [CountryRegionCode], [CreditLimitLCY], [PaymentTermsCode], [SalesPersonCode], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[No], Source.[Name], Source.[NoName], Source.[Address], Source.[Address2], Source.[City], Source.[PostCode], Source.[County], Source.[CountryRegionCode], Source.[TerritoryCode], Source.[CreditLimit(LCY)], Source.[PaymentTermsCode], Source.[SalesPersonCode], Source.[RowID], Source.[BatchDate])\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Customer <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "f14c8f26-058f-47ce-bc0e-c464ab53614b",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.063"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedGlobalDim1] "
            ],
            "metadata": {
                "azdata_cell_guid": "59f10378-f251-42a9-8451-869f69d8c2e1"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedGlobalDim1] \r\n",
                "@GlobalDim1 [dbo].[GlobalDimension1Type] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[DimensionValue]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "),\r\n",
                "\r\n",
                "tDimensionValue AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[DimensionCode]\r\n",
                "   ,[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,[GlobalDimensionNo]\r\n",
                "   ,Batchdate\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[DimensionValue]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[DimensionCode]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[GlobalDimensionNo]\r\n",
                "\t\t,Batchdate)\r\n",
                "\r\n",
                "MERGE [DW].[dGlobalDimension1] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\tDV.[Company]\r\n",
                "\t   ,DV.[DimensionCode]\r\n",
                "\t   ,DV.[Code]\r\n",
                "\t   ,DV.[Description]\r\n",
                "\t   ,DV.[GlobalDimensionNo]\r\n",
                "\t   ,DV.Batchdate\r\n",
                "\t   ,MR.[RowID]\r\n",
                "\tFROM tDimensionValue DV\r\n",
                "Right JOIN MaxRowID MR\r\n",
                "ON DV.Company = MR.Company\r\n",
                "AND DV.Code = MR.Code\r\n",
                "AND DV.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tAND Source.[GlobalDimensionNo] = '1'\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tAND Source.[GlobalDimensionNo] = '1'\r\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_GlobalDimension1 <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "ad7d5d1c-6503-41a7-8e97-75b82b829eb9",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.066"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedGlobalDim2]"
            ],
            "metadata": {
                "azdata_cell_guid": "2dccb010-41f6-4017-abdf-246b5b20c0ff"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedGlobalDim2] \r\n",
                "@GlobalDim2 [dbo].[GlobalDimension2Type] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[DimensionValue]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "),\r\n",
                "\r\n",
                "tDimensionValue AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[DimensionCode]\r\n",
                "   ,[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,[GlobalDimensionNo]\r\n",
                "   ,Batchdate\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[DimensionValue]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[DimensionCode]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[GlobalDimensionNo]\r\n",
                "\t\t,Batchdate)\r\n",
                "\r\n",
                "MERGE [DW].[dGlobalDimension2] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\tDV.[Company]\r\n",
                "\t   ,DV.[DimensionCode]\r\n",
                "\t   ,DV.[Code]\r\n",
                "\t   ,DV.[Description]\r\n",
                "\t   ,DV.[GlobalDimensionNo]\r\n",
                "\t   ,DV.Batchdate\r\n",
                "\t   ,MR.[RowID]\r\n",
                "\tFROM tDimensionValue DV\r\n",
                "Right JOIN MaxRowID MR\r\n",
                "ON DV.Company = MR.Company\r\n",
                "AND DV.Code = MR.Code\r\n",
                "AND DV.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tAND Source.[GlobalDimensionNo] = '2'\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tAND Source.[GlobalDimensionNo] = '2'\r\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_GlobalDimension2 <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "c85aff35-979c-4c4f-bcf4-24501220b2c7",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.062"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedItem]"
            ],
            "metadata": {
                "azdata_cell_guid": "b1847534-8833-49b1-8b7c-8279de6e4546"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedItem] \r\n",
                "\r\n",
                "@Item [dbo].[ItemType] ReadOnly\r\n",
                "\r\n",
                " AS \r\n",
                "Begin\r\n",
                "WITH\r\n",
                "MAXRowID as(Select [Company]\r\n",
                "\t   ,[No], MAX([RowID]) as [RowID]\r\n",
                "FROM [Stg].[Item]\r\n",
                "Group by [Company]\r\n",
                "\t   ,[No]),\r\n",
                "tItem as (\r\n",
                "SELECT\r\n",
                "\t\tI.[Company]\r\n",
                "\t   ,I.[No]\r\n",
                "\t   ,I.[Description]\r\n",
                "\t   ,C.[Description] AS 'Category'\r\n",
                "\t   ,G.[Description] AS 'ProductGroup'\r\n",
                "\t   ,I.[Inventory Posting Group]\r\n",
                "\t   ,I.[Base Unit of Measure]\r\n",
                "\t   ,I.[Item Capacity Value]\r\n",
                "\t   ,I.[Unit Price]\r\n",
                "\t   ,I.[LastItemCost]\r\n",
                "\t   ,I.[Vendor No_]\r\n",
                "\t   ,I.[Vendor Item No_]\r\n",
                "\t   ,D.[DivisionName]\r\n",
                "\t   ,MAX(I.[RowID]) as [RowID]\r\n",
                "\t   ,CONVERT(DATE, GETUTCDATE()) AS 'BatchDate'\r\n",
                "\tFROM [Stg].[Item] I\r\n",
                "\tLEFT JOIN [Stg].[ItemCategory] C\r\n",
                "\t\tON I.Company = C.Company\r\n",
                "\t\tAND I.[Item Category Code] = C.Code\r\n",
                "\tLEFT JOIN [Stg].[ProductGroup] G\r\n",
                "\t\tON I.Company = G.Company\r\n",
                "\t\tAND I.[Product Group Code] = G.Code\r\n",
                "\tLEFT JOIN [Stg].[Division] D\r\n",
                "\t\tON I.Company = D.Company\r\n",
                "\t\tAND I.[Division Code] = D.DivisionCode\r\n",
                "\tGROUP BY I.[Company]\r\n",
                "\t\t\t,I.[No]\r\n",
                "\t\t\t,I.[Description]\r\n",
                "\t\t\t,C.[Description]\r\n",
                "\t\t\t,G.[Description]\r\n",
                "\t\t\t,I.[Inventory Posting Group]\r\n",
                "\t\t\t,I.[Base Unit of Measure]\r\n",
                "\t\t\t,I.[Item Capacity Value]\r\n",
                "\t\t\t,I.[Unit Price]\r\n",
                "\t\t\t,I.[LastItemCost]\r\n",
                "\t\t\t,I.[Vendor No_]\r\n",
                "\t\t\t,I.[Vendor Item No_]\r\n",
                "\t\t\t,D.[DivisionName]\r\n",
                ")\r\n",
                "\r\n",
                "MERGE [DW].[dItem] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\tI.[Company]\r\n",
                "\t   ,I.[No]\r\n",
                "\t   ,I.[Description]\r\n",
                "\t   ,I.[Category]\r\n",
                "\t   ,I.[ProductGroup]\r\n",
                "\t   ,I.[Inventory Posting Group]\r\n",
                "\t   ,I.[Base Unit of Measure]\r\n",
                "\t   ,I.[Item Capacity Value]\r\n",
                "\t   ,I.[Unit Price]\r\n",
                "\t   ,I.[LastItemCost]\r\n",
                "\t   ,I.[Vendor No_]\r\n",
                "\t   ,I.[Vendor Item No_]\r\n",
                "\t   ,I.[DivisionName]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,I.[BatchDate]\r\n",
                "\r\n",
                "\tFROM tItem I\r\n",
                "\tRight JOIN MAXRowID Mr\r\n",
                "\tON I.Company = Mr.Company\r\n",
                "\tAND I.[No] = Mr.[No]\r\n",
                "AND I.RowID = Mr.RowID\r\n",
                "\t) AS Source\r\n",
                "\r\n",
                "\r\n",
                "ON Target.[No] = Source.[No]\r\n",
                "\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[Category] = Source.[Category]\r\n",
                "\t\t   ,[ProductGroup] = Source.[ProductGroup]\r\n",
                "\t\t   ,[InventoryPostingGroup] = Source.[Inventory Posting Group]\r\n",
                "\t\t   ,[BaseUnitOfMeasure] = Source.[Base Unit of Measure]\r\n",
                "\t\t   ,[Item Capacity Value] = Source.[Item Capacity Value]\r\n",
                "\t\t   ,[UnitPrice] = Source.[Unit Price]\r\n",
                "\t\t   ,[LastItemCost] = Source.[LastItemCost]\r\n",
                "\t\t   ,[VendorNo] = Source.[Vendor No_]\r\n",
                "\t\t   ,[VendorItemNo] = Source.[Vendor Item No_]\r\n",
                "\t\t   ,[Division] = Source.[DivisionName]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[Batchdate] = Source.[Batchdate]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [No], [Description], [Category], [ProductGroup], [InventoryPostingGroup], [BaseUnitOfMeasure], [Item Capacity Value], [UnitPrice], [LastItemCost], [VendorNo], [VendorItemNo], [Division], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[No], Source.[Description], Source.[Category], Source.[ProductGroup], Source.[Inventory Posting Group], Source.[Base Unit of Measure], Source.[Item Capacity Value], Source.[Unit Price], Source.[LastItemCost], Source.[Vendor No_], Source.[Vendor Item No_], Source.[DivisionName], Source.[RowID], Source.[BatchDate])\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Item <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                "\r\n",
                ";\r\n",
                "\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "417e291d-edb3-4cb6-a432-a5e9d8402262",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.095"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedItemCategory]"
            ],
            "metadata": {
                "azdata_cell_guid": "9dce6394-e281-4185-a551-1d9612178022"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedItemCategory] \r\n",
                "@ItemCategory [dbo].[ItemCategoryType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH\r\n",
                "MAXRowID as (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[ItemCategory]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]),\r\n",
                "tItemCategory AS (SELECT\r\n",
                "\t\t[Company]\r\n",
                "\t   ,[Code]\r\n",
                "\t   ,[Description]\r\n",
                "\t   ,[DivisionCode]\r\n",
                "\t   ,MAX([RowID]) AS [RowID]\r\n",
                "\t   ,[BatchDate]\r\n",
                "\tFROM [Stg].[ItemCategory]\r\n",
                "\tGROUP BY [Company]\r\n",
                "\t\t\t,[Code]\r\n",
                "\t\t\t,[Description]\r\n",
                "\t\t\t,[DivisionCode]\r\n",
                "\t\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dItemCategory AS Target\r\n",
                "USING ( SELECT\r\n",
                "IC.[Company]\r\n",
                "\t   ,IC.[Code]\r\n",
                "\t   ,IC.[Description]\r\n",
                "\t   ,IC.[DivisionCode]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,IC.[BatchDate]\r\n",
                "\tFROM tItemCategory IC\r\n",
                "Right JOIN MAXRowID Mr\r\n",
                "\tON IC.Company = Mr.Company\r\n",
                "\tAND IC.[Code] = Mr.[Code]\r\n",
                "\tAND IC.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[DivisionCode] = Source.[DivisionCode]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [DivisionCode], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[DivisionCode], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_ItemCategory <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "060f1c15-9879-4460-a3b1-14f605886ca1",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.357"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 7
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedItemVariant]"
            ],
            "metadata": {
                "azdata_cell_guid": "49f5d0ae-cddf-48bd-a47c-335eb55e6036"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedItemVariant] \r\n",
                "@ItemVariant [dbo].[ItemVariantType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Item No_]\r\n",
                "   ,[Code]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[ItemVariant]\r\n",
                "GROUP BY [Item No_]\r\n",
                "\t\t,[Code]), \r\n",
                "tItemVariant AS (SELECT\r\n",
                "\t[timestamp]\r\n",
                "   ,[Item No_]\r\n",
                "   ,[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,[Description 2]\r\n",
                "   ,[Common Item No_]\r\n",
                "   ,[BatchDate]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[ItemVariant]\r\n",
                "GROUP BY [timestamp]\r\n",
                "\t\t,[Item No_]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[Description 2]\r\n",
                "\t\t,[Common Item No_]\r\n",
                "\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE [DW].[dItemVariant] AS Target\r\n",
                "USING ( SELECT\r\n",
                "IV.[timestamp]\r\n",
                "\t   ,IV.[Item No_]\r\n",
                "\t   ,IV.[Code]\r\n",
                "\t   ,IV.[Description]\r\n",
                "\t   ,IV.[Description 2]\r\n",
                "\t   ,IV.[Common Item No_]\r\n",
                "\t   ,IV.[BatchDate]\r\n",
                "\t   ,Mr.[RowID] \r\n",
                "\tFROM tItemVariant IV\r\n",
                "Right JOIN MAXRowID Mr\r\n",
                "\tON IV.[Item No_] = Mr.[Item No_]\r\n",
                "\tAND IV.[Code] = Mr.[Code]\r\n",
                "\tAND IV.RowID = Mr.RowID\r\n",
                "\t) AS Source\r\n",
                "\r\n",
                "ON Target.[Item No_] = Source.[Item No_]\r\n",
                "\tAND Target.[Code] = Source.[Code]\r\n",
                "\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Item No_] = Source.[Item No_]\r\n",
                "\t\t   ,[Code] = Source.[Code]\r\n",
                "\t\t   ,[Description] = Source.[Description]\r\n",
                "\t\t   ,[Description 2] = Source.[Description 2]\r\n",
                "\t\t   ,[Common Item No_] = Source.[Common Item No_]\r\n",
                "\t\t   ,[Batchdate] = Source.[Batchdate]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Item No_], [Code], [Description], [Description 2], [Common Item No_], [Batchdate], [RowID])\r\n",
                "\t\t\tVALUES (Source.[Item No_], Source.[Code], Source.[Description], Source.[Description 2], Source.[Common Item No_], Source.[Batchdate], Source.RowID)\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "5cfd1e38-3cdb-42de-90e5-de281923d942",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.064"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 8
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedLocation]"
            ],
            "metadata": {
                "azdata_cell_guid": "5bb15f31-4d32-4916-9d40-1cb4ff6ad0a2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedLocation] \r\n",
                "@Location [dbo].[LocationType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[LocationCode]\r\n",
                "FROM [Stg].[Location]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[LocationCode]), \r\n",
                "tLocation AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[ReportingStore]\r\n",
                "   ,[ReportingStoreName]\r\n",
                "   ,[LocationCode]\r\n",
                "   ,[IsStore]\r\n",
                "   ,[IsLocation]\r\n",
                "   ,[LocationName]\r\n",
                "   ,[StoreManagerID]\r\n",
                "   ,[PostCode]\r\n",
                "   ,[City]\r\n",
                "   ,[County]\r\n",
                "   ,[CountryCode]\r\n",
                "   ,[Longitude]\r\n",
                "   ,[Latitude]\r\n",
                "   ,[Store size (square meters)]\r\n",
                "FROM [Stg].[Location]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[ReportingStore]\r\n",
                "\t\t,[ReportingStoreName]\r\n",
                "\t\t,[LocationCode]\r\n",
                "\t\t,[IsStore]\r\n",
                "\t\t,[IsLocation]\r\n",
                "\t\t,[LocationName]\r\n",
                "\t\t,[StoreManagerID]\r\n",
                "\t\t,[PostCode]\r\n",
                "\t\t,[City]\r\n",
                "\t\t,[County]\r\n",
                "\t\t,[CountryCode]\r\n",
                "\t\t,[Longitude]\r\n",
                "\t\t,[Latitude]\r\n",
                "\t\t,[Store size (square meters)])\r\n",
                "\r\n",
                "MERGE DW.dLocation AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\tL.[Company]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,L.[ReportingStore]\r\n",
                "\t   ,L.[ReportingStoreName]\r\n",
                "\t   ,L.[LocationCode]\r\n",
                "\t   ,L.[IsStore]\r\n",
                "\t   ,L.[IsLocation]\r\n",
                "\t   ,L.[LocationName]\r\n",
                "\t   ,L.[StoreManagerID]\r\n",
                "\t   ,L.[PostCode]\r\n",
                "\t   ,L.[City]\r\n",
                "\t   ,L.[County]\r\n",
                "\t   ,L.[CountryCode]\r\n",
                "\t   ,L.[Longitude]\r\n",
                "\t   ,L.[Latitude]\r\n",
                "\t   ,L.[Store size (square meters)]\r\n",
                "\tFROM tLocation L\r\n",
                "Right JOIN MAXRowID Mr\r\n",
                "ON L.Company = Mr.Company\r\n",
                "AND L.LocationCode = Mr.LocationCode\r\n",
                "AND L.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[LocationCode] = Source.[LocationCode]\r\n",
                "\tAND Target.[Company] = Source.[company]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [RowID] = Source.[RowID]\r\n",
                "\t\t   ,[ReportingStore] = Source.[ReportingStore]\r\n",
                "\t\t   ,[ReportingStoreName] = Source.[ReportingStoreName]\r\n",
                "\t\t   ,[LocationCode] = Source.[LocationCode]\r\n",
                "\t\t   ,[IsStore] = Source.[IsStore]\r\n",
                "\t\t   ,[IsLocation] = Source.[IsLocation]\r\n",
                "\t\t   ,[LocationName] = Source.[LocationName]\r\n",
                "\t\t   ,[StoreManagerID] = Source.[StoreManagerID]\r\n",
                "\t\t   ,[PostCode] = Source.[PostCode]\r\n",
                "\t\t   ,[City] = Source.[City]\r\n",
                "\t\t   ,[County] = Source.[County]\r\n",
                "\t\t   ,[CountryCode] = Source.[CountryCode]\r\n",
                "\t\t   ,[Longitude] = Source.[Longitude]\r\n",
                "\t\t   ,[Latitude] = Source.[Latitude]\r\n",
                "\t\t   ,[Store size (square meters)] = Source.[Store size (square meters)]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [RowID], [ReportingStore], [ReportingStoreName], [LocationCode], [IsStore], [IsLocation], [LocationName], [StoreManagerID], [PostCode], [City], [County], [CountryCode], [Longitude], [Latitude], [Store size (square meters)])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[RowID], Source.[ReportingStore], Source.[ReportingStoreName], Source.[LocationCode], Source.[IsStore], Source.[IsLocation], Source.[LocationName], Source.[StoreManagerID], Source.[PostCode], Source.[City], Source.[County], Source.[CountryCode], Source.[Longitude], Source.[Latitude], Source.[Store size (square meters)])\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Location <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "2c6f3877-8b0f-497c-9a0d-a5edb980363e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.066"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 9
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedMember]"
            ],
            "metadata": {
                "azdata_cell_guid": "697018d8-0be9-4729-a41b-b59830e25e25"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedMember] \r\n",
                "@Member [dbo].[MemberType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID as (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[CardNo]\r\n",
                "   ,[ContactNo]\r\n",
                "\t,MAX(RowID) AS RowID\r\n",
                "FROM [Stg].[MembershipCard]\r\n",
                "GROUP BY\r\n",
                "[Company]\r\n",
                "   ,[CardNo]\r\n",
                "   ,[ContactNo]\r\n",
                "), \r\n",
                "tMembers AS ( SELECT\r\n",
                "\t\tMS.[Company]\r\n",
                "\t   ,MS.[CardNo]\r\n",
                "\t   ,M.[AccountNo]\r\n",
                "\t   ,MS.[ContactNo]\r\n",
                "\t   ,MS.[Status]\r\n",
                "\t   ,CASE\r\n",
                "\t\t\tWHEN MS.[Status] = 0 THEN 'Free'\r\n",
                "\t\t\tWHEN MS.[Status] = 1 THEN 'Allocated'\r\n",
                "\t\t\tWHEN MS.[Status] = 2 THEN 'Active'\r\n",
                "\t\t\tWHEN MS.[Status] = 3 THEN 'Blocked'\r\n",
                "\t\t\tELSE 'N/A'\r\n",
                "\t\tEND AS StatusDescription\r\n",
                "\t   ,MC.[Description] AS ClubDescription\r\n",
                "\t   ,SC.[Description] AS SchemeDescription\r\n",
                "\t   ,M.[FullName]\r\n",
                "\t   ,M.[FirstName]\r\n",
                "\t   ,M.[MiddleName]\r\n",
                "\t   ,M.[Surname]\r\n",
                "\t   ,M.[Address]\r\n",
                "\t   ,M.[Address2]\r\n",
                "\t   ,M.[City]\r\n",
                "\t   ,M.[PostCode]\r\n",
                "\t   ,M.[County]\r\n",
                "\t   ,M.[Country]\r\n",
                "\t   ,M.[Email]\r\n",
                "\t   ,M.[DateOfBirth]\r\n",
                "\t   ,M.[Age]\r\n",
                "\t   ,CASE\r\n",
                "\t\t\tWHEN M.[Gender] = 0 THEN 'No gender'\r\n",
                "\t\t\tWHEN M.[Gender] = 1 THEN 'Male'\r\n",
                "\t\t\tWHEN M.[Gender] = 2 THEN 'Female'\r\n",
                "\t\t\tELSE 'No gender'\r\n",
                "\t\tEND AS Gender\r\n",
                "\r\n",
                "\t   ,CASE\r\n",
                "\t\t\tWHEN M.[MaritalStatus] = 0 THEN 'No Marital Status'\r\n",
                "\t\t\tWHEN M.[MaritalStatus] = 1 THEN 'Single'\r\n",
                "\t\t\tWHEN M.[MaritalStatus] = 2 THEN 'Married'\r\n",
                "\t\t\tWHEN M.[MaritalStatus] = 3 THEN 'Divorced'\r\n",
                "\t\t\tWHEN M.[MaritalStatus] = 4 THEN 'Widowed'\r\n",
                "\t\t\tELSE 'No Marital Status'\r\n",
                "\t\tEND AS MaritalStatus\r\n",
                "\t   ,M.[SalutationCode]\r\n",
                "\t   ,MS.[FirstDateUsed]\r\n",
                "\t   ,ISNULL\r\n",
                "\t\t((SELECT TOP (1)\r\n",
                "\t\t\t\tCAST(Date AS DATE) AS Expr1\r\n",
                "\t\t\tFROM Stg.MemberSalesEntry AS MS\r\n",
                "\t\t\tWHERE (MS.MemberContactNo = M.ContactNo)\r\n",
                "\t\t\tAND (MS.Company = M.Company)\r\n",
                "\t\t\tORDER BY Date DESC)\r\n",
                "\t\t, '2000-01-01') AS LastPurchaseDate\r\n",
                "\t   ,CAST(ISNULL\r\n",
                "\t\t((SELECT TOP (1)\r\n",
                "\t\t\t\tDATEDIFF(dd, Date, GETDATE()) AS Expr1\r\n",
                "\t\t\tFROM Stg.MemberSalesEntry AS MS\r\n",
                "\t\t\tWHERE (MS.MemberContactNo = M.ContactNo)\r\n",
                "\t\t\tAND (MS.Company = M.Company)\r\n",
                "\t\t\tORDER BY Date DESC)\r\n",
                "\t\t, 999) AS INT) AS DaysSinceLastPurchase\r\n",
                "\t   ,MAX(MS.RowID) AS RowID\r\n",
                "\t   ,CONVERT(DATE, GETDATE()) AS BatchDate\r\n",
                "\r\n",
                "\tFROM [Stg].[MembershipCard] MS\r\n",
                "\tLEFT OUTER JOIN (SELECT\r\n",
                "\t\t\t*\r\n",
                "\t\tFROM [Stg].[MemberContact]\r\n",
                "\t\tWHERE RowID IN (SELECT\r\n",
                "\t\t\t\tMAX(RowID)\r\n",
                "\t\t\tFROM [Stg].[MemberContact]\r\n",
                "\t\t\tGROUP BY [Company]\r\n",
                "\t\t\t\t\t,[ContactNo])) M\r\n",
                "\t\tON MS.[Company] = M.Company\r\n",
                "\t\tAND MS.[ContactNo] = M.[ContactNo]\r\n",
                "\tLEFT OUTER JOIN [Stg].[MemberClub] MC\r\n",
                "\t\tON MS.[Company] = MC.Company\r\n",
                "\t\tAND MS.[ClubCode] = MC.[Code]\r\n",
                "\tLEFT OUTER JOIN [Stg].[MemberScheme] SC\r\n",
                "\t\tON MS.[Company] = SC.Company\r\n",
                "\t\tAND MS.[SchemeCode] = SC.[Code]\r\n",
                "\tGROUP BY MS.[Company]\r\n",
                "\t\t\t,M.[Company]\r\n",
                "\t\t\t,MS.[CardNo]\r\n",
                "\t\t\t,M.[AccountNo]\r\n",
                "\t\t\t,MS.[ContactNo]\r\n",
                "\t\t\t,M.[ContactNo]\r\n",
                "\t\t\t,MS.[Status]\r\n",
                "\t\t\t,MC.[Description]\r\n",
                "\t\t\t,SC.[Description]\r\n",
                "\t\t\t,M.[FullName]\r\n",
                "\t\t\t,M.[FirstName]\r\n",
                "\t\t\t,M.[MiddleName]\r\n",
                "\t\t\t,M.[Surname]\r\n",
                "\t\t\t,M.[Address]\r\n",
                "\t\t\t,M.[Address2]\r\n",
                "\t\t\t,M.[City]\r\n",
                "\t\t\t,M.[PostCode]\r\n",
                "\t\t\t,M.[County]\r\n",
                "\t\t\t,M.[Country]\r\n",
                "\t\t\t,M.[Email]\r\n",
                "\t\t\t,M.[DateOfBirth]\r\n",
                "\t\t\t,M.[Age]\r\n",
                "\t\t\t,M.[Gender]\r\n",
                "\t\t\t,M.[MaritalStatus]\r\n",
                "\t\t\t,M.[SalutationCode]\r\n",
                "\t\t\t,MS.[FirstDateUsed])\r\n",
                "\r\n",
                "\r\n",
                "MERGE [DW].[dMember] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\ttM.[Company]\r\n",
                "\t   ,tM.[CardNo]\r\n",
                "\t   ,tM.[AccountNo]\r\n",
                "\t   ,tM.[ContactNo]\r\n",
                "\t   ,tM.[Status]\r\n",
                "\t   ,tM.StatusDescription\r\n",
                "\t   ,tM.ClubDescription\r\n",
                "\t   ,tM.SchemeDescription\r\n",
                "\t   ,tM.[FullName]\r\n",
                "\t   ,tM.[FirstName]\r\n",
                "\t   ,tM.[MiddleName]\r\n",
                "\t   ,tM.[Surname]\r\n",
                "\t   ,tM.[Address]\r\n",
                "\t   ,tM.[Address2]\r\n",
                "\t   ,tM.[City]\r\n",
                "\t   ,tM.[PostCode]\r\n",
                "\t   ,tM.[County]\r\n",
                "\t   ,tM.[Country]\r\n",
                "\t   ,tM.[Email]\r\n",
                "\t   ,tM.[DateOfBirth]\r\n",
                "\t   ,tM.[Age]\r\n",
                "\t   ,tM.Gender\r\n",
                "       ,tM.MaritalStatus\r\n",
                "\t   ,tM.[SalutationCode]\r\n",
                "\t   ,tM.[FirstDateUsed]\r\n",
                "\t   ,tM.LastPurchaseDate\r\n",
                "\t   ,tM.DaysSinceLastPurchase\r\n",
                "\t   ,Mr.RowID\r\n",
                "\t   ,tM.BatchDate\r\n",
                "\tFROM tMembers tM\r\n",
                "\tRIGHT JOIN MaxRowID Mr\r\n",
                "\tON tM.Company = Mr.Company\r\n",
                "\tAND tM.CardNo = Mr.CardNo\r\n",
                "\tAND tM.ContactNo = Mr.ContactNo\r\n",
                "    AND tM.RowID = Mr.RowID\r\n",
                "\t) AS Source\r\n",
                "\r\n",
                "\r\n",
                "ON Target.[Company] = source.[Company]\r\n",
                "\tAND Target.[CardNo] = Source.[CardNo]\r\n",
                "\tAND Target.[ContactNo] = Source.[ContactNo]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Company] = source.[Company]\r\n",
                "\t\t   ,[CardNo] = source.[CardNo]\r\n",
                "\t\t   ,[ContactNo] = source.[ContactNo]\r\n",
                "\t\t   ,[AccountNo] = source.[AccountNo]\r\n",
                "\t\t   ,[CardStatus] = source.[Status]\r\n",
                "\t\t   ,[CardStatusDescription] = source.[StatusDescription]\r\n",
                "\t\t   ,[ClubDescription] = source.[ClubDescription]\r\n",
                "\t\t   ,[SchemeDescription] = source.[SchemeDescription]\r\n",
                "\t\t   ,[FullName] = source.[FullName]\r\n",
                "\t\t   ,[FirstName] = source.[FirstName]\r\n",
                "\t\t   ,[MiddleName] = source.[MiddleName]\r\n",
                "\t\t   ,[Surname] = source.[Surname]\r\n",
                "\t\t   ,[Address] = source.[Address]\r\n",
                "\t\t   ,[Address2] = source.[Address2]\r\n",
                "\t\t   ,[City] = source.[City]\r\n",
                "\t\t   ,[PostCode] = source.[PostCode]\r\n",
                "\t\t   ,[County] = source.[County]\r\n",
                "\t\t   ,[Country] = source.[Country]\r\n",
                "\t\t   ,[Email] = source.[Email]\r\n",
                "\t\t   ,[DateOfBirth] = source.[DateOfBirth]\r\n",
                "\t\t   ,[Age] = source.[Age]\r\n",
                "\t\t   ,[Gender] = source.[Gender]\r\n",
                "\t\t   ,[MaritalStatus] = source.[MaritalStatus]\r\n",
                "\t\t   ,[SalutationCode] = source.[SalutationCode]\r\n",
                "\t\t   ,[FirstDateUsed] = source.[FirstDateUsed]\r\n",
                "\t\t   ,[LastPurchaseDate] = source.[LastPurchaseDate]\r\n",
                "\t\t   ,[DaysSinceLastPurchase] = source.[DaysSinceLastPurchase]\r\n",
                "\t\t   ,[RowID] = source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = source.[BatchDate]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [CardNo], [ContactNo], [AccountNo], [CardStatus], [CardStatusDescription], [ClubDescription], [SchemeDescription], [FullName], [FirstName], [MiddleName], [Surname], [Address], [Address2], [City], [PostCode], [County], [Country], [Email], [DateOfBirth], [Age], [Gender], [MaritalStatus], [SalutationCode], [FirstDateUsed], [LastPurchaseDate], [DaysSinceLastPurchase], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[CardNo], Source.[ContactNo], Source.[AccountNo], Source.[Status], Source.[StatusDescription], Source.[ClubDescription], Source.[SchemeDescription], Source.[FullName], Source.[FirstName], Source.[MiddleName], Source.[Surname], Source.[Address], Source.[Address2], Source.[City], Source.[PostCode], Source.[County], Source.[Country], Source.[Email], Source.[DateOfBirth], Source.[Age], Source.[Gender], Source.[MaritalStatus], Source.[SalutationCode], Source.[FirstDateUsed], Source.[LastPurchaseDate], Source.[DaysSinceLastPurchase], Source.[RowID], Source.[BatchDate])\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Member <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f870d8f7-b788-44fa-82f7-c08f637ac6fa",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.086"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 10
        },
        {
            "cell_type": "markdown",
            "source": [
                "No need to change [MergedMemberAttributes]"
            ],
            "metadata": {
                "azdata_cell_guid": "d7053843-414a-4d99-8c12-47b1273db5e4"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedMembershipCard]"
            ],
            "metadata": {
                "azdata_cell_guid": "75374eea-adcd-44d7-97dc-b01dbb151c73"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedMembershipCard] \r\n",
                "@MembershipCard [dbo].[MembershipCardType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\tMS.[Company]\r\n",
                "   ,MS.[CardNo]\r\n",
                "   ,MAX(MS.[RowID]) AS [RowID]\r\n",
                "FROM [Stg].[MembershipCard] MS\r\n",
                "GROUP BY MS.[Company]\r\n",
                "\t\t,MS.[CardNo]\r\n",
                "\t\t), \r\n",
                "\r\n",
                "tMembershipCard AS (SELECT\r\n",
                "\tMS.[Company]\r\n",
                "   ,MS.[CardNo]\r\n",
                "   ,MS.[Status]\r\n",
                "   ,CASE\r\n",
                "\t\tWHEN MS.[Status] = 0 THEN 'Free'\r\n",
                "\t\tWHEN MS.[Status] = 1 THEN 'Allocated'\r\n",
                "\t\tWHEN MS.[Status] = 2 THEN 'Active'\r\n",
                "\t\tWHEN MS.[Status] = 3 THEN 'Blocked'\r\n",
                "\t\tELSE 'N/A'\r\n",
                "\tEND AS StatusDescription\r\n",
                "   ,MS.[LinkedToAccount]\r\n",
                "   ,MC.[Description] AS ClubDescription\r\n",
                "   ,SC.[Description] AS SchemeDescription\r\n",
                "   ,MS.[AccountNo]\r\n",
                "   ,MS.[ContactNo]\r\n",
                "   ,MS.[DateCreated]\r\n",
                "   ,MS.[CreatedBy]\r\n",
                "   ,MS.[NoSeries]\r\n",
                "   ,MS.[AllocatedToStore]\r\n",
                "   ,MS.[FirstDateUsed]\r\n",
                "   ,MS.[LastValidDate]\r\n",
                "   ,MS.[ReasonBlocked]\r\n",
                "   ,MS.[DateBlocked]\r\n",
                "   ,MS.[BlockedBy]\r\n",
                "   ,MS.[BatchDate]\r\n",
                "   ,MAX(MS.[RowID]) AS [RowID]\r\n",
                "FROM [Stg].[MembershipCard] MS\r\n",
                "LEFT OUTER JOIN [Stg].[MemberClub] MC\r\n",
                "\tON MS.[Company] = MC.Company\r\n",
                "\tAND MS.[ClubCode] = MC.[Code]\r\n",
                "LEFT OUTER JOIN [Stg].[MemberScheme] SC\r\n",
                "\tON MS.[Company] = SC.Company\r\n",
                "\tAND MS.[SchemeCode] = SC.[Code]\r\n",
                "GROUP BY MS.[Company]\r\n",
                "\t\t,MS.[CardNo]\r\n",
                "\t\t,MS.[Status]\r\n",
                "\t\t,MS.[LinkedToAccount]\r\n",
                "\t\t,MC.[Description]\r\n",
                "\t\t,SC.[Description]\r\n",
                "\t\t,MS.[AccountNo]\r\n",
                "\t\t,MS.[ContactNo]\r\n",
                "\t\t,MS.[DateCreated]\r\n",
                "\t\t,MS.[CreatedBy]\r\n",
                "\t\t,MS.[NoSeries]\r\n",
                "\t\t,MS.[AllocatedToStore]\r\n",
                "\t\t,MS.[FirstDateUsed]\r\n",
                "\t\t,MS.[LastValidDate]\r\n",
                "\t\t,MS.[ReasonBlocked]\r\n",
                "\t\t,MS.[DateBlocked]\r\n",
                "\t\t,MS.[BlockedBy]\r\n",
                "\t\t,MS.[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dMembershipCard AS Target\r\n",
                "USING ( SELECT\r\n",
                "tMSC.[Company]\r\n",
                "\t   ,tMSC.[CardNo]\r\n",
                "\t   ,tMSC.[Status]\r\n",
                "\t   ,tMSC.StatusDescription\r\n",
                "\t   ,tMSC.[LinkedToAccount]\r\n",
                "\t   ,tMSC.ClubDescription\r\n",
                "\t   ,tMSC.SchemeDescription\r\n",
                "\t   ,tMSC.[AccountNo]\r\n",
                "\t   ,tMSC.[ContactNo]\r\n",
                "\t   ,tMSC.[DateCreated]\r\n",
                "\t   ,tMSC.[CreatedBy]\r\n",
                "\t   ,tMSC.[NoSeries]\r\n",
                "\t   ,tMSC.[AllocatedToStore]\r\n",
                "\t   ,tMSC.[FirstDateUsed]\r\n",
                "\t   ,tMSC.[LastValidDate]\r\n",
                "\t   ,tMSC.[ReasonBlocked]\r\n",
                "\t   ,tMSC.[DateBlocked]\r\n",
                "\t   ,tMSC.[BlockedBy]\r\n",
                "\t   ,tMSC.[BatchDate]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\tFROM tMembershipCard tMSC\r\n",
                "    RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tMSC.Company = Mr.Company\r\n",
                "\tAND tMSC.CardNo = Mr.CardNo\r\n",
                "\tAND tMSC.RowID = Mr.RowID\r\n",
                "\t) Source\r\n",
                "ON Target.[CardNo] = Source.[CardNo]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Status] = Source.[Status]\r\n",
                "\t\t   ,[StatusDescription] = Source.[StatusDescription]\r\n",
                "\t\t   ,[LinkedToAccount] = Source.[LinkedToAccount]\r\n",
                "\t\t   ,[ClubDescription] = Source.[ClubDescription]\r\n",
                "\t\t   ,[SchemeDescription] = Source.[SchemeDescription]\r\n",
                "\t\t   ,[AccountNo] = Source.[AccountNo]\r\n",
                "\t\t   ,[ContactNo] = Source.[ContactNo]\r\n",
                "\t\t   ,[DateCreated] = Source.[DateCreated]\r\n",
                "\t\t   ,[CreatedBy] = Source.[CreatedBy]\r\n",
                "\t\t   ,[NoSeries] = Source.[NoSeries]\r\n",
                "\t\t   ,[AllocatedToStore] = Source.[AllocatedToStore]\r\n",
                "\t\t   ,[FirstDateUsed] = Source.[FirstDateUsed]\r\n",
                "\t\t   ,[LastValidDate] = Source.[LastValidDate]\r\n",
                "\t\t   ,[ReasonBlocked] = Source.[ReasonBlocked]\r\n",
                "\t\t   ,[DateBlocked] = Source.[DateBlocked]\r\n",
                "\t\t   ,[BlockedBy] = Source.[BlockedBy]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[Batchdate] = Source.[Batchdate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [CardNo], [Status], [StatusDescription], [LinkedToAccount], [ClubDescription], [SchemeDescription], [AccountNo], [ContactNo], [DateCreated], [CreatedBy], [NoSeries], [AllocatedToStore], [FirstDateUsed], [LastValidDate], [ReasonBlocked], [DateBlocked], [BlockedBy], [RowID], [Batchdate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[CardNo], Source.[Status], Source.[StatusDescription], Source.[LinkedToAccount], Source.[ClubDescription], Source.[SchemeDescription], Source.[AccountNo], Source.[ContactNo], Source.[DateCreated], Source.[CreatedBy], Source.[NoSeries], Source.[AllocatedToStore], Source.[FirstDateUsed], Source.[LastValidDate], Source.[ReasonBlocked], Source.[DateBlocked], Source.[BlockedBy], Source.[RowID], Source.[Batchdate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_MembershipCard <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "b973ed8b-fcf8-4693-b0c2-427958b36c5e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.070"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 11
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedOffers]"
            ],
            "metadata": {
                "azdata_cell_guid": "0e926c51-0635-4923-a4a1-76af4bec5b80"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedOffers] \r\n",
                "@Item [dbo].[OffersType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS\r\n",
                "(SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No_]\r\n",
                "   ,[Offer Type]\r\n",
                "    ,MAX(RowID) AS RowID\r\n",
                "FROM [Stg].[Offers]\r\n",
                "group by \r\n",
                "[Company]\r\n",
                "   ,[No_]\r\n",
                "   ,[Offer Type]),\r\n",
                "tOffers AS\r\n",
                "(SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No_]\r\n",
                "   ,[Table Source]\r\n",
                "   ,[Description]\r\n",
                "   ,[Status]\r\n",
                "   ,[StatusDescription]\r\n",
                "   ,[Offer Type]\r\n",
                "   ,[OfferTypeDescription]\r\n",
                "   ,[Price Group]\r\n",
                "   ,[ValidationPeriodID]\r\n",
                "   ,[Batchdate]\r\n",
                "   ,MAX(RowID) AS RowID\r\n",
                "FROM [Stg].[Offers]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No_]\r\n",
                "\t\t,[Table Source]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[Status]\r\n",
                "\t\t,[StatusDescription]\r\n",
                "\t\t,[Offer Type]\r\n",
                "\t\t,[OfferTypeDescription]\r\n",
                "\t\t,[Price Group]\r\n",
                "\t\t,[ValidationPeriodID]\r\n",
                "\t\t,[Batchdate]\r\n",
                "\r\n",
                "UNION ALL\r\n",
                "\r\n",
                "SELECT\r\n",
                "\tCompany\r\n",
                "   ,TDE.[Offer No_] AS No_\r\n",
                "   ,'Transaction' AS [Table Source]\r\n",
                "   ,'Missing from Discounts' AS [Description]\r\n",
                "   ,-1 AS Status\r\n",
                "   ,'N/A' AS StatusDescription\r\n",
                "   ,[Offer Type]\r\n",
                "   ,[Offer Type Description] AS OfferTypeDescription\r\n",
                "   ,'N/A' AS [Price Group]\r\n",
                "   ,'' AS ValidationPeriodID\r\n",
                "   ,CAST(GETUTCDATE() AS DATE) AS Batchdate\r\n",
                "   ,MAX(RowID) AS RowID\r\n",
                "\r\n",
                "FROM [Stg].[TransDiscountEntry] TDE\r\n",
                "WHERE TDE.[Offer No_]-- In @@MissingOffers\r\n",
                "IN (SELECT DISTINCT\r\n",
                "\t\t[Offer No_]\r\n",
                "\tFROM stg.[TransDiscountEntry]\r\n",
                "\tWHERE [Offer No_] NOT IN (SELECT\r\n",
                "\t\t\tNo_\r\n",
                "\t\tFROM stg.Offers))\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,TDE.[Offer No_]\r\n",
                "\t\t,[Offer Type]\r\n",
                "\t\t,[Offer Type Description])\r\n",
                "\r\n",
                "MERGE [DW].[dOffers] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\ttOf.[Company]\r\n",
                "\t   ,tOf.[No_]\r\n",
                "\t   ,tOf.[Table Source]\r\n",
                "\t   ,tOf.[Description]\r\n",
                "\t   ,tOf.[Status]\r\n",
                "\t   ,tOf.[StatusDescription]\r\n",
                "\t   ,tOf.[Offer Type]\r\n",
                "\t   ,tOf.[OfferTypeDescription]\r\n",
                "\t   ,tOf.[Price Group]\r\n",
                "\t   ,tOf.[ValidationPeriodID]\r\n",
                "\t   ,tOf.[Batchdate]\r\n",
                "\t   ,Mr.RowID\r\n",
                "\tFROM tOffers tOf\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tOf.Company = Mr.Company\r\n",
                "\tAND tOf.No_ = Mr.No_\r\n",
                "\tAND tOf.[Offer Type] = Mr.[Offer Type]\r\n",
                "    AND tOf.RowID = Mr.RowID\r\n",
                "\t) AS Source\r\n",
                "\r\n",
                "ON Target.[No_] = Source.[No_]\r\n",
                "\tAND Target.[Offer Type] = Source.[Offer Type]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Company] = Source.[Company]\r\n",
                "\t\t   ,[No_] = Source.[No_]\r\n",
                "\t\t   ,[Table Source] = Source.[Table Source]\r\n",
                "\t\t   ,[Description] = Source.[Description]\r\n",
                "\t\t   ,[Status] = Source.[Status]\r\n",
                "\t\t   ,[StatusDescription] = Source.[StatusDescription]\r\n",
                "\t\t   ,[Offer Type] = Source.[Offer Type]\r\n",
                "\t\t   ,[OfferTypeDescription] = Source.[OfferTypeDescription]\r\n",
                "\t\t   ,[Price Group] = Source.[Price Group]\r\n",
                "\t\t   ,[ValidationPeriodID] = Source.[ValidationPeriodID]\r\n",
                "\t\t   ,[Batchdate] = Source.[Batchdate]\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [No_], [Table Source], [Description], [Status], [StatusDescription], [Offer Type], [OfferTypeDescription], [Price Group], [ValidationPeriodID], [Batchdate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[No_], Source.[Table Source], Source.[Description], Source.[Status], Source.[StatusDescription], Source.[Offer Type], Source.[OfferTypeDescription], Source.[Price Group], Source.[ValidationPeriodID], Source.[Batchdate])\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Offer <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "360ce284-597e-4958-a839-767208d66abb",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.127"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 12
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedPOSTerminal]"
            ],
            "metadata": {
                "azdata_cell_guid": "1fbe3b0c-e516-40fc-bf0f-4f17fca0ddd4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedPOSTerminal] \r\n",
                "@POSTerminal [dbo].[POSTerminalType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[POSTerminal]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]\r\n",
                "\t), \r\n",
                "tPOSTerminal AS \r\n",
                "\t\t(SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,[StoreNo]\r\n",
                "   ,[Description]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[BatchDate]\r\n",
                "FROM [Stg].[POSTerminal]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]\r\n",
                "\t\t,[StoreNo]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dPOSTerminal AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\ttPT.[Company]\r\n",
                "\t   ,tPT.[No]\r\n",
                "\t   ,tPT.[StoreNo]\r\n",
                "\t   ,tPT.[Description]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tPT.[BatchDate]\r\n",
                "\tFROM tPOSTerminal tPT\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tPT.Company = Mr.Company\r\n",
                "\tAND tPT.[No] = Mr.[No]\r\n",
                "    AND tPT.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[No] = Source.[No]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [StoreNo] = Source.[StoreNo]\r\n",
                "\t\t   ,[Description] = Source.[Description]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [No], [StoreNo], [Description], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[No], Source.[StoreNo], Source.[Description], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_POSTerminal <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "ea649f12-e8af-47d7-bae0-c7fb4bb7b703",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.067"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 13
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedProductGroup]"
            ],
            "metadata": {
                "azdata_cell_guid": "0a4f8fbe-62ef-479e-adc1-f4d12aeb780a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedProductGroup] \r\n",
                "@ProductGroup [dbo].[ProductGroupType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "    ,MAX([RowID]) AS [RowID]\r\n",
                "  FROM [DW].[dProductGroup]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "\t), \r\n",
                "\r\n",
                "tProductGroup AS \r\n",
                "\t\t( SELECT\r\n",
                "\t[SK_ProductGroup]\r\n",
                "   ,[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,[ItemCategoryCode]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[Batchdate]\r\n",
                "FROM [DW].[dProductGroup]\r\n",
                "GROUP BY [SK_ProductGroup]\r\n",
                "\t\t,[Company]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[ItemCategoryCode]\r\n",
                "\t\t,[Batchdate])\r\n",
                "\r\n",
                "MERGE DW.dProductGroup AS Target\r\n",
                "USING ( SELECT\r\n",
                "\ttPG.[SK_ProductGroup]\r\n",
                "\t   ,tPG.[Company]\r\n",
                "\t   ,tPG.[Code]\r\n",
                "\t   ,tPG.[Description]\r\n",
                "\t   ,tPG.[ItemCategoryCode]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tPG.[Batchdate]\r\n",
                "\tFROM tProductGroup tPG\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tPG.Company = Mr.Company\r\n",
                "\tAND tPG.[Code] = Mr.[Code]\r\n",
                "    AND tPG.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[ItemCategoryCode] = Source.[ItemCategoryCode]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [ItemCategoryCode], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[ItemCategoryCode], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_ProductGroup <> -1\r\n",
                "\tTHEN DELETE\r\n",
                "\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "48387bfe-b3a8-4197-b621-0a6c61636cb0",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.061"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedProductPostingGroup]"
            ],
            "metadata": {
                "azdata_cell_guid": "31c39da1-3c98-4208-a8b1-3ab93743c8eb"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedProductPostingGroup] \r\n",
                "@ProductPostingGroup [dbo].[ProductPostingGroupType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "    ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[ProductPostingGroup]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "),\r\n",
                "\t\t \r\n",
                "tProductPostingGroup AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[Batchdate]\r\n",
                "FROM [Stg].[ProductPostingGroup]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[Batchdate])\r\n",
                "\r\n",
                "MERGE [DW].[dProductPostingGroup] AS Target\r\n",
                "USING ( SELECT\r\n",
                "\t\ttPPG.[Company]\r\n",
                "\t   ,tPPG.[Code]\r\n",
                "\t   ,tPPG.[Description]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tPPG.[Batchdate]\r\n",
                "\tFROM tProductPostingGroup tPPG\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tPPG.Company = Mr.Company\r\n",
                "\tAND tPPG.[Code] = Mr.[Code]\r\n",
                "    AND tPPG.RowID = Mr.RowID\r\n",
                ") AS Source\r\n",
                "ON Target.[Code] = Source.[Code]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [Description] = Source.[Description]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [Code], [Description], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[Code], Source.[Description], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_ProductPostingGroup <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "9a797451-f799-4889-b414-be36ad2cd6b3",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.062"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedReturnReason]"
            ],
            "metadata": {
                "azdata_cell_guid": "96697638-453a-4a98-a513-55ae567d94f0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER\tProcedure [dbo].[MergedReturnReason]\r\n",
                "@ReturnReason dbo.ReturnReasonType ReadOnly\r\n",
                "AS\r\n",
                "Begin\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Code]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[ReturnReason]\r\n",
                "GROUP BY [Code]\r\n",
                "), \r\n",
                "\r\n",
                "tReturnReason AS (SELECT\r\n",
                "\t[Code]\r\n",
                "   ,[Description]\r\n",
                "   ,[SourceTable]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,CONVERT(DATE, GETUTCDATE()) AS 'BatchDate'\r\n",
                "FROM [Stg].[ReturnReason]\r\n",
                "GROUP BY [Code]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[SourceTable])\r\n",
                "\r\n",
                "Merge DW.dReturnReason as Target\r\n",
                "using ( SELECT\r\n",
                "\t\ttRR.[Code]\r\n",
                "\t   ,tRR.[Description]\r\n",
                "\t   ,tRR.[SourceTable]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tRR.BatchDate\r\n",
                "\tFROM tReturnReason tRR\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tRR.[Code] = Mr.[Code]\r\n",
                "    AND tRR.RowID = Mr.RowID) AS Source\r\n",
                "\r\n",
                "ON Source.Code = Target.Code\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET Description = Source.Description\r\n",
                "\t\t   ,SourceTable = Source.SourceTable\r\n",
                "\t\t   ,[Batchdate] = Source.BatchDate\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT (Code, Description, SourceTable, BatchDate)\r\n",
                "\t\t\tVALUES (Source.Code, Source.Description, Source.SourceTable, Source.BatchDate)\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_ReturnReason <> -1\r\n",
                "\tTHEN DELETE\r\n",
                "\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "bc88505a-8280-45f3-bc2e-300dd89d0e2b",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.070"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 16
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix for [MergedSalesBudget]"
            ],
            "metadata": {
                "azdata_cell_guid": "daa52074-0506-40d2-ad0a-7bd635e26d24"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedSalesBudget] \r\n",
                "@SalesBudget [dbo].[SalesBudgetType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "With MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Name]\r\n",
                "    ,MAX([RowID]) AS [RowID]\r\n",
                " FROM [Stg].[RetailSalesBudgetName]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Name]\r\n",
                "), \r\n",
                "\r\n",
                "tSalesBudget AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[Name]\r\n",
                "   ,[Description]\r\n",
                "   ,[Status]\r\n",
                "   ,[Revision]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[BatchDate]\r\n",
                "FROM [Stg].[RetailSalesBudgetName]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[Name]\r\n",
                "\t\t,[Description]\r\n",
                "\t\t,[Status]\r\n",
                "\t\t,[Revision]\r\n",
                "\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dSalesBudget AS Target\r\n",
                "USING ( SELECT\r\n",
                "tSB.[Company]\r\n",
                "\t   ,tSB.[Name]\r\n",
                "\t   ,tSB.[Description]\r\n",
                "\t   ,tSB.[Status]\r\n",
                "\t   ,tSB.[Revision]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tSB.[BatchDate]\r\n",
                "\tFROM tSalesBudget tSB\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tSB.Company = Mr.Company\r\n",
                "\tAND tSB.[Name] = Mr.[Name]\r\n",
                "    AND tSB.RowID = Mr.RowID) AS Source\r\n",
                "\r\n",
                "ON Target.[BudgetName] = Source.[Name]\r\n",
                "\tAND Target.[Company] = Source.[Company]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [BudgetName] = Source.[Name]\r\n",
                "\t\t   ,[BudgetDescription] = Source.[Description]\r\n",
                "\t\t   ,[Status] = Source.[Status]\r\n",
                "\t\t   ,[Revision] = Source.[Revision]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [BudgetName], [BudgetDescription], [Status], [Revision], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[Name], Source.[Description], Source.[Status], Source.[Revision], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_SalesBudget <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "8eff9cf7-69e6-465c-bb39-4c3454c31bb0",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.062"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 17
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedSalesDocument] NOT needed..."
            ],
            "metadata": {
                "azdata_cell_guid": "36da957d-aa7c-4ac1-b51d-4481619d9973"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedStaff]"
            ],
            "metadata": {
                "azdata_cell_guid": "7abfc52e-0746-47a8-86b0-2879804aa3ef"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedStaff] \r\n",
                "@Staff [dbo].[StaffType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[ID]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[Staff]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[ID]\r\n",
                "\t\t), \r\n",
                "\r\n",
                "tStaff as (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[ID]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[FirstName]\r\n",
                "   ,[LastName]\r\n",
                "   ,[BatchDate]\r\n",
                "   ,[SourceTable]\r\n",
                "  FROM [Stg].[Staff]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[ID]\r\n",
                "\t\t,[FirstName]\r\n",
                "\t\t,[LastName]\r\n",
                "\t\t,[BatchDate]\r\n",
                "\t\t,[SourceTable]\r\n",
                "\t\t)\r\n",
                "\r\n",
                "MERGE [DW].[dStaff] AS Target\r\n",
                "USING ( SELECT\r\n",
                "tS.[Company]\r\n",
                "\t   ,tS.[ID]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tS.[FirstName]\r\n",
                "\t   ,tS.[LastName]\r\n",
                "\t   ,tS.[BatchDate]\r\n",
                "\t   ,tS.[SourceTable]\r\n",
                "\tFROM tStaff tS\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tS.Company = Mr.Company\r\n",
                "\tAND tS.[ID] = Mr.[ID]\r\n",
                "\tAND tS.RowID = Mr.RowID) AS Source\r\n",
                "ON Target.[ID] = Source.[ID]\r\n",
                "\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [FirstName] = Source.[FirstName]\r\n",
                "\t\t   ,[LastName] = Source.[LastName]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [ID], [FirstName], [LastName], [RowID], [BatchDate])\r\n",
                "\t\t\tVALUES (Source.[Company], source.[ID], Source.[FirstName], Source.[FirstName], Source.[RowID], Source.[BatchDate])\r\n",
                "\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Staff <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount"
            ],
            "metadata": {
                "azdata_cell_guid": "0b5230b8-cf75-40ea-9948-f272831894a9",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.062"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 18
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedStore]"
            ],
            "metadata": {
                "azdata_cell_guid": "f7fbff8c-4901-4993-861d-ffc365d9a47d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedStore] \r\n",
                "@Store [dbo].[StoreType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "\r\n",
                "WITH MaxRowID AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[StoreNo]\r\n",
                "    ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[Store]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[StoreNo]\r\n",
                "\t\t), \r\n",
                "\r\n",
                "tStore AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[StoreNo]\r\n",
                "   ,[StoreName]\r\n",
                "   ,[StoreManagerID]\r\n",
                "   ,[PostCode]\r\n",
                "   ,[City]\r\n",
                "   ,[County]\r\n",
                "   ,[CountryCode]\r\n",
                "   ,[LocationCode]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[Batchdate]\r\n",
                "FROM [Stg].[Store]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[StoreNo]\r\n",
                "\t\t,[StoreName]\r\n",
                "\t\t,[StoreManagerID]\r\n",
                "\t\t,[PostCode]\r\n",
                "\t\t,[City]\r\n",
                "\t\t,[County]\r\n",
                "\t\t,[CountryCode]\r\n",
                "\t\t,[LocationCode]\r\n",
                "\t\t,[Batchdate])\r\n",
                "\r\n",
                "MERGE DW.dStore AS Target\r\n",
                "USING ( SELECT\r\n",
                "tS.[Company]\r\n",
                "\t   ,tS.[StoreNo]\r\n",
                "\t   ,tS.[StoreName]\r\n",
                "\t   ,tS.[StoreManagerID]\r\n",
                "\t   ,tS.[PostCode]\r\n",
                "\t   ,tS.[City]\r\n",
                "\t   ,tS.[County]\r\n",
                "\t   ,tS.[CountryCode]\r\n",
                "\t   ,tS.[LocationCode]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tS.[Batchdate]\r\n",
                "\tFROM tStore tS\r\n",
                "\tRIGHT JOIN MaxRowID Mr\r\n",
                "\tON tS.Company = Mr.Company\r\n",
                "\tAND tS.[StoreNo] = Mr.[StoreNo]\r\n",
                "    AND tS.RowID = Mr.RowID) AS Source\r\n",
                "ON Target.[StoreNo] = Source.[StoreNo]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [StoreName] = Source.[StoreName]\r\n",
                "\t\t   ,[StoreManagerID] = Source.[StoreManagerID]\r\n",
                "\t\t   ,[PostCode] = Source.[PostCode]\r\n",
                "\t\t   ,[City] = Source.[City]\r\n",
                "\t\t   ,[County] = Source.[County]\r\n",
                "\t\t   ,[CountryCode] = Source.[CountryCode]\r\n",
                "\t\t   ,[LocationCode] = Source.[LocationCode]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [StoreNo], [StoreName], [StoreManagerID], [PostCode], [City], [County], [CountryCode], [LocationCode], [RowID], [Batchdate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[StoreNo], Source.[StoreName], Source.[StoreManagerID], Source.[PostCode], Source.[City], Source.[County], Source.[CountryCode], Source.[LocationCode], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Store <> -1\r\n",
                "\tTHEN DELETE;\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "dace7dae-759e-44cb-962f-d0a60db3a9c3",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.071"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 19
        },
        {
            "cell_type": "markdown",
            "source": [
                "Fix For [MergedVendor]"
            ],
            "metadata": {
                "azdata_cell_guid": "ff687718-27df-482d-a9dc-23b9533fd50e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "ALTER PROCEDURE [dbo].[MergedVendor] \r\n",
                "@Vendor [dbo].[VendorType] ReadOnly\r\n",
                " AS \r\n",
                "Begin\r\n",
                "WITH MaxRowID AS\r\n",
                "(SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "FROM [Stg].[Vendor]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]\r\n",
                "\t\t), \r\n",
                "\r\n",
                "tVendor AS (SELECT\r\n",
                "\t[Company]\r\n",
                "   ,[No]\r\n",
                "   ,[Name]\r\n",
                "   ,[TerritoryCode]\r\n",
                "   ,[NoName]\r\n",
                "   ,[CountryRegionCode]\r\n",
                "   ,[Address]\r\n",
                "   ,[Address 2]\r\n",
                "   ,[City]\r\n",
                "   ,[Currency Code]\r\n",
                "   ,[Language Code]\r\n",
                "   ,[Blocked]\r\n",
                "   ,[Post Code]\r\n",
                "   ,[County]\r\n",
                "   ,MAX([RowID]) AS [RowID]\r\n",
                "   ,[BatchDate]\r\n",
                "FROM [Stg].[Vendor]\r\n",
                "GROUP BY [Company]\r\n",
                "\t\t,[No]\r\n",
                "\t\t,[Name]\r\n",
                "\t\t,[TerritoryCode]\r\n",
                "\t\t,[NoName]\r\n",
                "\t\t,[CountryRegionCode]\r\n",
                "\t\t,[Address]\r\n",
                "\t\t,[Address 2]\r\n",
                "\t\t,[City]\r\n",
                "\t\t,[Currency Code]\r\n",
                "\t\t,[Language Code]\r\n",
                "\t\t,[Blocked]\r\n",
                "\t\t,[Post Code]\r\n",
                "\t\t,[County]\r\n",
                "\t\t,[BatchDate])\r\n",
                "\r\n",
                "MERGE DW.dVendor AS Target\r\n",
                "USING ( SELECT\r\n",
                "tV.[Company]\r\n",
                "\t   ,tV.[No]\r\n",
                "\t   ,tV.[Name]\r\n",
                "\t   ,tV.[TerritoryCode]\r\n",
                "\t   ,tV.[NoName]\r\n",
                "\t   ,tV.[CountryRegionCode]\r\n",
                "\t   ,tV.[Address]\r\n",
                "\t   ,tV.[Address 2]\r\n",
                "\t   ,tV.[City]\r\n",
                "\t   ,tV.[Currency Code]\r\n",
                "\t   ,tV.[Language Code]\r\n",
                "\t   ,tV.[Blocked]\r\n",
                "\t   ,tV.[Post Code]\r\n",
                "\t   ,tV.[County]\r\n",
                "\t   ,Mr.[RowID]\r\n",
                "\t   ,tV.[BatchDate]\r\n",
                "\tFROM tVendor tV\r\n",
                "RIGHT JOIN MaxRowID Mr\r\n",
                "\tON tV.Company = Mr.Company\r\n",
                "\tAND tV.[No] = Mr.[No]\r\n",
                "    AND tV.RowID = Mr.RowID) AS Source\r\n",
                "ON Target.[VendorNo] = Source.[No]\r\n",
                "WHEN MATCHED\r\n",
                "\tTHEN UPDATE\r\n",
                "\t\tSET [VendorName] = Source.[Name]\r\n",
                "\t\t   ,[VendorNoName] = Source.[NoName]\r\n",
                "\t\t   ,[TerritoryCode] = Source.[TerritoryCode]\r\n",
                "\t\t   ,[CountryRegionCode] = Source.[CountryRegionCode]\r\n",
                "\t\t   ,[RowID] = Source.[RowID]\r\n",
                "\t\t   ,[BatchDate] = Source.[BatchDate]\r\n",
                "\t\t   ,[Address] = Source.[Address]\r\n",
                "\t\t   ,[Address 2] = Source.[Address 2]\r\n",
                "\t\t   ,[City] = Source.[City]\r\n",
                "\t\t   ,[Currency Code] = Source.[Currency Code]\r\n",
                "\t\t   ,[Language Code] = Source.[Language Code]\r\n",
                "\t\t   ,[Blocked] = Source.[Blocked]\r\n",
                "\t\t   ,[Post Code] = Source.[Post Code]\r\n",
                "\t\t   ,[County] = Source.[County]\r\n",
                "WHEN NOT MATCHED BY TARGET\r\n",
                "\tTHEN INSERT ([Company], [VendorNo], [VendorName], [VendorNoName], [TerritoryCode], [CountryRegionCode], [RowID], [Batchdate])\r\n",
                "\t\t\tVALUES (Source.[Company], Source.[No], Source.[Name], Source.[NoName], Source.[TerritoryCode], Source.[CountryRegionCode], Source.[RowID], Source.[BatchDate])\r\n",
                "--WHEN NOT MATCHED BY SOURCE THEN DELETE;\r\n",
                "WHEN NOT MATCHED BY SOURCE\r\n",
                "\tAND Target.SK_Vendor <> -1\r\n",
                "\tTHEN DELETE\r\n",
                ";\r\n",
                "END\r\n",
                "\r\n",
                "SELECT\r\n",
                "\t'RowCount' = @@rowcount\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "a004bbb4-b5f0-4a64-a6e3-5b7b4e7a81e2",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Commands completed successfully."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.064"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 20
        }
    ]
}